package DB;


import java.sql.SQLException;
import java.sql.Statement;


import javax.servlet.http.HttpServlet;

import java.sql.Connection;
import java.util.Properties;

/**
 * Servlet implementation class InitDB
 */
public class InitDB extends HttpServlet {
	private static final long serialVersionUID = 1L;

	/**
	 * @see Servlet#init()
	 */
	@Override
    public void init() {
		Statement stmt = null;
		Connection con = null;
		
		try {	
			
			Properties p = new Properties();
			p.load(getServletContext().getResourceAsStream("/WEB-INF/config"));
			con = (new ConnectToDB(p)).getConnection();
					
		    stmt = con.createStatement(); 
			
		    //create user table
			stmt.executeUpdate("CREATE TABLE IF NOT EXISTS user "             +
			                   "(username NVARCHAR(8) NOT NULL, "             + 
			                   "password NVARCHAR(8) NOT NULL, "              + 
                               "first_name NVARCHAR(15) NOT NULL, "           + 
                               "last_name NVARCHAR(15) NOT NULL, "            +
                               "phone_number NVARCHAR(15) NOT NULL, "         +
                               "administrator BOOL NOT NULL, "                +  
                               "PRIMARY KEY (username));");
			
			//create course table
			stmt.executeUpdate("CREATE TABLE IF NOT EXISTS course "              +
	                   		   "(course_title NVARCHAR(15) NOT NULL, "           + 
	                   		   "description NVARCHAR(100) NOT NULL, "            + 
	                   		   "number_credit_points INTEGER NOT NULL, "         + 
	                   		   "course_capacity INTEGER NOT NULL, "              +
	                   		   "group_number INTEGER NOT NULL, "                 +
                    		   "PRIMARY KEY (course_title));");	
			
			//create user_course table 
			stmt.executeUpdate("CREATE TABLE IF NOT EXISTS user_course "                        +
            		   		   "(course_title NVARCHAR(15) NOT NULL, "                          + 
            		           "username NVARCHAR(8) NOT NULL, "             		            +
         		   	           "PRIMARY KEY (course_title,username), "                          +
         		   	           "FOREIGN KEY (course_title) REFERENCES course(course_title), "   +
         		   	           "FOREIGN KEY (username) REFERENCES user(username));");	
			
			//create course_sessions table 
			stmt.executeUpdate("CREATE TABLE IF NOT EXISTS course_sessions "                       +
            		   		   "(course_title NVARCHAR(15) NOT NULL, "      	                   + 
            		           "start_time_slot INTEGER NOT NULL, "             	               +
            		           "end_time_slot INTEGER NOT NULL, "             		               +
         		   	           "PRIMARY KEY(course_title,start_time_slot,end_time_slot), "         +
         		   	           "FOREIGN KEY (course_title) REFERENCES course(course_title));");	
		
			}
		
			catch (SQLException e) {e.printStackTrace();} 
			catch (Exception e) {e.printStackTrace();} 
			
			finally { // Close the connection.
				if(stmt !=null){
				     try{stmt.close();}	catch(Exception e){e.printStackTrace();} }
				if(con !=null){
				     try{con.close();}	catch(Exception e){e.printStackTrace();} }
			}
	}

}
